#! /bin/env python3

#Author: Ahmed Abdulshafy
#Created: 10:00 AM - 21 Jul, 2013
#Last Modification: Tuesday, 13 August 2013, 11:37 PM

import sqlite3
import re
import datetime

def cdr_create_db(db_engine="sqlite3"):
	if (db_engine == "sqlite3"):
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()

		#create the main cdrs table
		sql_str = "CREATE TABLE IF NOT EXISTS cdrs (" +\
			"cdr_id INTEGER PRIMARY KEY AUTOINCREMENT, "+\
			"ocg CHAR, "+\
			"fcd CHAR, "+\
			"time_orig INTEGER, "+\
			"time_con INTEGER, "+\
			"duration INTEGER, "+\
			"money REAL)"
		cur.execute(sql_str)

		#create rating tables
		sql_str = "CREATE TABLE IF NOT EXISTS num_ptrns (" +\
			"ptrn_id INTEGER PRIMARY KEY AUTOINCREMENT, "+\
			"ptrn_desc CHAR, "+\
			"pattern CHAR)"
		cur.execute(sql_str)
		sql_str = "CREATE TABLE IF NOT EXISTS time_ranges (" +\
			"tr_id INTEGER PRIMARY KEY AUTOINCREMENT, "+\
			"tr_desc CHAR, "+\
			"tr_start INTEGER, "+\
			"tr_end INTEGER, "+\
			"periodic CHAR)"
		cur.execute(sql_str)
		sql_str = "CREATE TABLE IF NOT EXISTS rate (" +\
			"tr_id INTEGER, "+\
			"clg_ptrn_id INTEGER, "+\
			"cld_ptrn_id INTEGER, "+\
			"money REAL, "+\
			"FOREIGN KEY(tr_id) REFERENCES time_ranges(tr_id), "+\
			"FOREIGN KEY(clg_ptrn_id) REFERENCES num_ptrns(ptrn_id), "+\
			"FOREIGN KEY(cld_ptrn_id) REFERENCES num_ptrns(ptrn_id), "+\
			"PRIMARY KEY(tr_id, clg_ptrn_id, cld_ptrn_id))"
		cur.execute(sql_str)

		conn.commit()
		conn.close()

def init_db(db_engine="sqlite3"):
	if (db_engine == "sqlite3"):
		#create default patterns
		default_rate = 2.0
		default_pattern = '.*'

		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()

		#test to see if the tables contains anything or not
		sql_str = "SELECT * FROM rate"
		cur.execute(sql_str)

		if len(cur.fetchall())==0:
			sql_str = "INSERT INTO num_ptrns (ptrn_desc, pattern) VALUES ('all', '{0}')".format(default_pattern)
			cur.execute(sql_str)
			conn.commit()
			sql_str = "INSERT INTO time_ranges(tr_desc, tr_start, tr_end, periodic) "+\
				"VALUES ('all times', 0, 0, 'n')"
			cur.execute(sql_str)
			conn.commit()
			cur.execute("SELECT * FROM num_ptrns")
			ptrn_id = cur.fetchone()[0] #ptrn_id temp workaround
			cur.execute("SELECT * FROM time_ranges")
			tr_id = cur.fetchone()[0] #tr_id temp workaround
			sql_str = "INSERT INTO rate(tr_id, clg_ptrn_id, cld_ptrn_id, money) "+\
				"VALUES ({0}, {1}, {1}, {2})".format(tr_id, ptrn_id, default_rate)
			cur.execute(sql_str)
			conn.commit()
		conn.close()
			
def cdr_wr(cdr, db_engine="sqlite3"):
	if (db_engine == "sqlite3"):
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()

		ocg = cdr["ocg"]
		fcd = cdr["fcd"]
		time_orig = cdr["t_orig"]
		time_con = cdr["t_con"]
		duration = cdr["dur"]
		money = cdr["money"]

		q_vars = (ocg, fcd, time_orig, time_con, duration, money)

		sql_str = "INSERT INTO cdrs (ocg, fcd, time_orig, time_con, duration, money) "+\
			"VALUES (?, ?, ?, ?, ?, ?)"

		cur.execute(sql_str, q_vars)

		conn.commit()
		conn.close()

def cdr_rd(filter=None, db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()

		sql_str = "SELECT rowid, ocg, fcd, time_orig, time_con, duration, money FROM cdrs"
		if filter!=None:
			sql_str += "WHERE "+filter
		cur.execute(sql_str)

		rt = cur.fetchall()
		conn.close()
		return rt

def rate_rd(cld_ptrn_id, clg_ptrn_id, tr_id, db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()

		sql_str = "SELECT money from rate WHERE clg_ptrn_id=? AND cld_ptrn_id=? AND tr_id=?"
		cur.execute(sql_str, (clg_ptrn_id, cld_ptrn_id, tr_id))
		rt = cur.fetchone()[-1] #money temp workaround
		conn.close()
		return rt

def rate_wr(cld_ptrn_id, clg_ptrn_id, tr_id, money, db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()
		#search for the given entry
		sql_str = "SELECT money from rate WHERE clg_ptrn_id=? AND cld_ptrn_id=? AND tr_id=?"
		cur.execute(sql_str, (clg_ptrn_id, cld_ptrn_id, tr_id))
		if len(cur.fetchall())!=0:
			sql_str = "UPDATE rate SET money=? WHERE clg_ptrn_id=? AND cld_ptrn_id=? AND tr_id=?"
		else:
			sql_str = "INSERT INTO rate(money, clg_ptrn_id, cld_ptrn_id, tr_id) VALUES (?, ?, ?, ?)"
		
		cur.execute(sql_str, (money, clg_ptrn_id, cld_ptrn_id, tr_id))
		conn.commit()
		conn.close()

def match_pattern(num, db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()
		#check if there's a pattern that fully represents num
		sql_str = "SELECT * FROM num_ptrns WHERE pattern=?"
		tup = (num,)
		cur.execute(sql_str, tup)
		res = cur.fetchall()
		if len(res) > 0:
			rt = res[0][0] #ptrn_id temp workaround
			conn.close()
			return rt
		else:
			#check for pattern that matches num
			sql_str = "SELECT * FROM num_ptrns EXCEPT SELECT * FROM num_ptrns WHERE pattern='*'"
			cur.execute(sql_str)
			for row in cur.fetchall():
				match_obj = re.match(row[2], num) #pattern temp workaround
				if match_obj and match_obj.group(0)==num:
					rt = row[0] #ptrn_id temp workaround
					conn.close()
					return rt
			#if execution reaches here, then no match is found above, match with the *
			sql_str = "SELECT * FROM num_ptrns WHERE pattern='*'"
			cur.execute(sql_str)
			rt = cur.fetchone()[0] #ptrn_id temp workaround
			conn.close()
			return rt

def match_time_range(time, db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()
		#currently the only time period is forever!
		sql_str = "SELECT * FROM time_ranges WHERE tr_start=0 AND tr_end=0"
		cur.execute(sql_str)
		rt = cur.fetchone()[0]
		conn.close()
		return rt

def pattern_wr(pattern, pattern_desc="", db_engine="sqlite3"):
	if db_engine=="sqlite3":
		conn = sqlite3.connect("cdrs.db")
		cur = conn.cursor()
		#if the pattern exists do nothing
		sql_str = "SELECT * FROM num_ptrns WHERE pattern=?"
		cur.execute(sql_str, (pattern,))
		if len(cur.fetchall())==0:
			sql_str = "INSERT INTO num_ptrns(pattern, ptrn_desc) VALUES(?, ?)"
			cur.execute(sql_str, (pattern, pattern_desc))
			conn.commit()
		conn.close()

if __name__ == "__main__":
	cdr_create_db("sqlite3")
	init_db()
	#cdr_wr("sqlite3", {"ocg": 4457, "fcd": 7090, "t_orig":35364777878, "t_con": 458764687, "dur": 46})
	#cdr_rd("sqlite3")
	pattern_wr(r'^ah*',"test")
	print(match_pattern('bhhh'))

